package tzg.dao;

import dbutils.JdbcTemplate;
import dbutils.ResultSetHandler;
import tzg.bean.Page;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDao {

    public void queryAllUserAndUserDetailByPage(Page<List<Map<String,Object>>> page) {
        StringBuilder sb=new StringBuilder("select ");
        sb.append("u.id,u.user_name,u.nick_name,u.password,u.sex,u.email,u.phone,np.name as np_name,ud.hobby_code,ud.birthday,ud.np_code ");
        sb.append("from uim_user u left outer join uim_user_detail ud on u.id=ud.user_id ");
        sb.append("left outer join uim_np np on ud.np_code=np.code where 1=1 ");
        if(!"".equals(page.getKeyword())) {
            sb.append("and u.user_name like ?");
        }
        sb.append(" limit ?,?");
        int beginPage=(page.getCurrentPage()-1)*page.getPageNumber();//定义起始页（0，2，4.....）
        int rowLength=page.getPageNumber();//定义每一页显示行数
        String keyword=page.getKeyword();//定义查询内容
        ResultSetHandler<List<Map<String,Object>>> rsh=new ListMapHandler();
        List<Map<String,Object>> list=null;
        if("".equals(keyword)) {
            list= JdbcTemplate.query(sb.toString(),rsh,beginPage,rowLength);
        }else {
            list=JdbcTemplate.query(sb.toString(),rsh,"%"+keyword+"%",beginPage,rowLength);
        }
        page.setList(list);
    }
    public int queryTotalRowNumber(){
        int totalNumber=0;
        String sql="select count(*) from uim_user u left outer join uim_user_detail ud on u.id=ud.user_id left outer join uim_np np on ud.np_code=np.code";
        totalNumber=JdbcTemplate.queryForCount(sql);
        return totalNumber;
    }
    public int queryTotalRowNumber(String keyword){
        int totalRowNumber=0;
        String sql="select count(*) from uim_user u left outer join uim_user_detail ud on u.id=ud.user_id left outer join uim_np np on ud.np_code=np.code "
                + "where u.user_name like ?";
        totalRowNumber=JdbcTemplate.queryForCount(sql,"%"+keyword+"%");//百分号意为0或多个字符
        return totalRowNumber;
    }
    public int deleteUser(int id) {
        int rowNumber=0;
        String sql="delete from uim_user where id="+id;
        rowNumber=JdbcTemplate.update(sql);
        return rowNumber;
    }


}
